﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Windows;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using Microsoft.Win32;
using System.Windows.Controls;

namespace VSWClient.Export
{
	public class NPOIToExcel
	{
		/// <summary>
		/// custom path,export single table data
		/// </summary>
		/// <param name="dt">data source</param>
		/// <param name="modefilename">exclel template path</param>
		/// <param name="modesheetname">sheet name</param>
		public static void ExportToExcel(System.Data.DataTable dt, string modefilename, string modesheetname)
		{
			if (!File.Exists(modefilename))
			{
				MessageBox.Show("未找到导出模板");
				return;
			}
			System.Windows.Forms.SaveFileDialog fileDialog = new System.Windows.Forms.SaveFileDialog();
			fileDialog.Filter = "EXECL文件(*.xls) |*.xls |所有文件(*.*) |*.*";
			if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
			{
				return;
			}
			//不允许dataGridView显示添加行，负责导出时会报最后一行未实例化错误
			//dataGridView1.AllowUserToAddRows = false;

			FileStream modefile = new FileStream(modefilename, FileMode.Open, FileAccess.Read);
			HSSFWorkbook hssfworkbook = new HSSFWorkbook(modefile);  //基于03以后的模板，可以导出任何类型文件，但必须引用using NPOI.HSSF.UserModel;
			ISheet sheet = hssfworkbook.GetSheet(modesheetname);
			int Rowhead = -1;
			int Rowfoot = -1;
			bool[] Rowfoottotal = new bool[100];
			double[] Rowfoottotalvalue = new double[100];

			//设置边框样式  
			HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
			style.BorderTop = BorderStyle.THIN;
			style.BorderLeft = BorderStyle.THIN;
			style.BorderRight = BorderStyle.THIN;
			style.BorderBottom = BorderStyle.THIN;

			for (int indexrow = 0; indexrow <= sheet.LastRowNum; indexrow++)   //检测表头
			{
				for (int indexcol = 0; indexcol < sheet.GetRow(indexrow).LastCellNum; indexcol++)  //写列
				{
					for (int i = 0; i < dt.Columns.Count; i++)
					{
						//判断数据集列是否等于xls列
						if ((sheet.GetRow(indexrow).GetCell(indexcol)) != null)
						{
							if ((sheet.GetRow(indexrow).GetCell(indexcol).StringCellValue.Trim()) == dt.Columns[i].Caption.Trim())
							{
								if (Rowhead == -1)
								{
									Rowhead = indexrow;
								}
								sheet.GetRow(indexrow).GetCell(indexcol).CellStyle = style;//加边框
							}
							if ((sheet.GetRow(indexrow).GetCell(indexcol).StringCellValue.Trim()) == "统计")
							{
								Rowfoot = indexrow;
								Rowfoottotal[indexcol] = true;
								Rowfoottotalvalue[indexcol] = 0;
								sheet.GetRow(indexrow).GetCell(indexcol).CellStyle = style;//加边框
							}
						}
					}
				}
			}
			if (Rowhead != -1)
			{
				for (int i = dt.Rows.Count - 1; i >= 0; i--)
				{					
					Rowfoot = Rowfoot + 1;
					sheet.ShiftRows(Rowhead+1, sheet.LastRowNum+1, 1, true, false);
					IRow row = sheet.CreateRow(1 + Rowhead);
					for (int indexcolxls = 0; indexcolxls < sheet.GetRow(Rowhead).LastCellNum; indexcolxls++)  //写列
					{
						for (int indexcoldb = 0; indexcoldb < dt.Columns.Count; indexcoldb++)
						{
							//判断数据集列是否等于xls列
							if ((sheet.GetRow(Rowhead).GetCell(indexcolxls).StringCellValue.Trim()) == dt.Columns[indexcoldb].Caption.ToString().Trim())
							{
								//写入该列每行数据
								if (Rowfoottotal[indexcolxls] == true)
								{
									row.CreateCell(indexcolxls).SetCellValue(Convert.ToDouble(dt.Rows[i][indexcoldb].ToString()));
									Rowfoottotalvalue[indexcolxls] = Rowfoottotalvalue[indexcolxls] + Convert.ToDouble(dt.Rows[i][indexcoldb].ToString());
								}
								else
								{
									row.CreateCell(indexcolxls).SetCellValue(dt.Rows[i][indexcoldb].ToString());
								}
							}
						}
					}
				}
			}
			if (Rowfoot != -1)
			{
				IRow row = sheet.GetRow(Rowfoot);
				for (int i = 0; i < 100; i++)
				{
					if (Rowfoottotal[i] == true)
					{
						row.CreateCell(i).SetCellValue(Rowfoottotalvalue[i]);
					}
				}
			}

			FileStream outfile = File.Create(fileDialog.FileName);
			hssfworkbook.Write(outfile);

			modefile.Close();
			outfile.Close();

			MessageBox.Show("导出数据成功!", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
			GC.Collect();
		}

		/// <summary>
		/// fixed path,export single table data
		/// </summary>
		/// <param name="dt">data source</param>
		/// <param name="modefilename">excel template path</param>
		/// <param name="modesheetname">sheet name</param>
		/// <param name="filename">export path</param>
		public static void ExportToExcel(System.Data.DataTable dt, string modefilename, string modesheetname, string filename)
		{
			if (!File.Exists(modefilename))
			{
				MessageBox.Show("未找到导出模板");
				return;
			}
			System.Windows.Forms.SaveFileDialog fileDialog = new System.Windows.Forms.SaveFileDialog();
			fileDialog.Filter = "EXECL文件(*.xls) |*.xls |所有文件(*.*) |*.*";
			//if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
			//{
			//    return;
			//}

			//不允许dataGridView显示添加行，负责导出时会报最后一行未实例化错误
			//dataGridView1.AllowUserToAddRows = false;

			FileStream modefile = new FileStream(modefilename, FileMode.Open, FileAccess.Read);
			HSSFWorkbook hssfworkbook = new HSSFWorkbook(modefile);  //基于03以后的模板，可以导出任何类型文件，但必须引用using NPOI.HSSF.UserModel;
			ISheet sheet = hssfworkbook.GetSheet(modesheetname);
			int Rowhead = -1;
			int Rowfoot = -1;
			bool[] Rowfoottotal = new bool[100];
			double[] Rowfoottotalvalue = new double[100];

			//设置边框样式  
			HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
			style.BorderTop = BorderStyle.THIN;
			style.BorderLeft = BorderStyle.THIN;
			style.BorderRight = BorderStyle.THIN;
			style.BorderBottom = BorderStyle.THIN;

			for (int indexrow = 0; indexrow <= sheet.LastRowNum; indexrow++)   //检测表头
			{
				for (int indexcol = 0; indexcol < sheet.GetRow(indexrow).LastCellNum; indexcol++)  //写列
				{
					for (int i = 0; i < dt.Columns.Count; i++)
					{
						//判断数据集列是否等于xls列
						if ((sheet.GetRow(indexrow).GetCell(indexcol)) != null)
						{
							if ((sheet.GetRow(indexrow).GetCell(indexcol).StringCellValue.Trim()) == dt.Columns[i].Caption.Trim())
							{
								if (Rowhead == -1)
								{
									Rowhead = indexrow;
								}
								sheet.GetRow(indexrow).GetCell(indexcol).CellStyle = style;//加边框
							}
							if ((sheet.GetRow(indexrow).GetCell(indexcol).StringCellValue.Trim()) == "统计")
							{
								Rowfoot = indexrow;
								Rowfoottotal[indexcol] = true;
								Rowfoottotalvalue[indexcol] = 0;
								sheet.GetRow(indexrow).GetCell(indexcol).CellStyle = style;//加边框
							}
						}
					}
				}
			}
			if (Rowhead != -1)
			{
				for (int i = dt.Rows.Count - 1; i >= 0; i--)
				{
					Rowfoot = Rowfoot + 1;
					sheet.ShiftRows(Rowhead + 1, sheet.LastRowNum, 1, true, false);
					IRow row = sheet.CreateRow(1 + Rowhead);
					for (int indexcolxls = 0; indexcolxls < sheet.GetRow(Rowhead).LastCellNum; indexcolxls++)  //写列
					{
						for (int indexcoldb = 0; indexcoldb < dt.Columns.Count; indexcoldb++)
						{
							//判断数据集列是否等于xls列
							if ((sheet.GetRow(Rowhead).GetCell(indexcolxls).StringCellValue.Trim()) == dt.Columns[indexcoldb].Caption.ToString().Trim())
							{
								//写入该列每行数据
								if (Rowfoottotal[indexcolxls] == true)
								{
									row.CreateCell(indexcolxls).SetCellValue(Convert.ToDouble(dt.Rows[i][indexcoldb].ToString()));
									Rowfoottotalvalue[indexcolxls] = Rowfoottotalvalue[indexcolxls] + Convert.ToDouble(dt.Rows[i][indexcoldb].ToString());
								}
								else
								{
									//object ob = dt.DefaultView[i][indexcoldb];
									object ob = dt.Rows[i][indexcoldb];
									if (ob.GetType() == typeof(System.String))
									{
										row.CreateCell(indexcolxls).SetCellValue(ob.ToString());
									}
									else if (ob.GetType() == typeof(System.Double))
									{
										row.CreateCell(indexcolxls).SetCellValue(Convert.ToDouble(ob));
									}
								}
							}
						}
					}
				}
			}
			if (Rowfoot != -1)
			{
				IRow row = sheet.GetRow(Rowfoot);
				for (int i = 0; i < 100; i++)
				{
					if (Rowfoottotal[i] == true)
					{
						row.CreateCell(i).SetCellValue(Rowfoottotalvalue[i]);
					}
				}
			}

			FileStream outfile = File.Create(filename);
			hssfworkbook.Write(outfile);

			modefile.Close();
			outfile.Close();

			MessageBox.Show("转换数据成功!", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
			GC.Collect();
		}

		/// <summary>
		/// custom path,export multiple sheets of a table
		/// </summary>
		/// <param name="dt">data source</param>
		/// <param name="modefilename">excel template path</param>
		/// <param name="lists">sheet list</param>
		public static void ExportToExcel(System.Data.DataTable[] dt, string modefilename, List<string> lists)
		{
			if (!File.Exists(modefilename))
			{
				MessageBox.Show("未找到导出模板");
				return;
			}
			System.Windows.Forms.SaveFileDialog fileDialog = new System.Windows.Forms.SaveFileDialog();
			fileDialog.Filter = "EXECL文件(*.xls) |*.xls |所有文件(*.*) |*.*";
			if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
			{
				return;
			}
			//不允许dataGridView显示添加行，负责导出时会报最后一行未实例化错误
			//dataGridView1.AllowUserToAddRows = false;
			
			FileStream modefile = new FileStream(modefilename, FileMode.Open, FileAccess.Read);
			HSSFWorkbook hssfworkbook = new HSSFWorkbook(modefile);  //基于03以后的模板，可以导出任何类型文件，但必须引用using NPOI.HSSF.UserModel;
			for (int j = 0; j < dt.Length; j++)
			{
				ISheet sheet = hssfworkbook.GetSheet(lists[j]);
				int Rowhead = -1;
				int Rowfoot = -1;
				bool[] Rowfoottotal = new bool[100];
				double[] Rowfoottotalvalue = new double[100];

				//设置边框样式  
				HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
				style.BorderTop = BorderStyle.THIN;
				style.BorderLeft = BorderStyle.THIN;
				style.BorderRight = BorderStyle.THIN;
				style.BorderBottom = BorderStyle.THIN;

				for (int indexrow = 0; indexrow <= sheet.LastRowNum; indexrow++)   //检测表头
				{
					for (int indexcol = 0; indexcol < sheet.GetRow(indexrow).LastCellNum; indexcol++)  //写列
					{
						for (int i = 0; i < dt[j].Columns.Count; i++)
						{
							//判断数据集列是否等于xls列
							if ((sheet.GetRow(indexrow).GetCell(indexcol)) != null)
							{
								if ((sheet.GetRow(indexrow).GetCell(indexcol).StringCellValue.Trim()) == dt[j].Columns[i].Caption.Trim())
								{
									if (Rowhead == -1)
									{
										Rowhead = indexrow;
									}
									sheet.GetRow(indexrow).GetCell(indexcol).CellStyle = style;//加边框
								}
								if ((sheet.GetRow(indexrow).GetCell(indexcol).StringCellValue.Trim()) == "统计")
								{
									Rowfoot = indexrow;
									Rowfoottotal[indexcol] = true;
									Rowfoottotalvalue[indexcol] = 0;
									sheet.GetRow(indexrow).GetCell(indexcol).CellStyle = style;//加边框
								}
							}
						}
					}
				}
				if (Rowhead != -1)
				{
					for (int i = dt[j].Rows.Count - 1; i >= 0; i--)
					{
						Rowfoot = Rowfoot + 1;
						sheet.ShiftRows(Rowhead + 1, sheet.LastRowNum + 1, 1, true, false);
						IRow row = sheet.CreateRow(1 + Rowhead);
						for (int indexcolxls = 0; indexcolxls < sheet.GetRow(Rowhead).LastCellNum; indexcolxls++)  //写列
						{
							for (int indexcoldb = 0; indexcoldb < dt[j].Columns.Count; indexcoldb++)
							{
								//判断数据集列是否等于xls列
								if ((sheet.GetRow(Rowhead).GetCell(indexcolxls).StringCellValue.Trim()) == dt[j].Columns[indexcoldb].Caption.ToString().Trim())
								{
									//写入该列每行数据
									if (Rowfoottotal[indexcolxls] == true)
									{
										row.CreateCell(indexcolxls).SetCellValue(Convert.ToDouble(dt[j].Rows[i][indexcoldb].ToString()));
										Rowfoottotalvalue[indexcolxls] = Rowfoottotalvalue[indexcolxls] + Convert.ToDouble(dt[j].Rows[i][indexcoldb].ToString());
									}
									else
									{
										row.CreateCell(indexcolxls).SetCellValue(dt[j].Rows[i][indexcoldb].ToString());
									}
								}
							}
						}
					}
				}
				if (Rowfoot != -1)
				{
					IRow row = sheet.GetRow(Rowfoot);
					for (int i = 0; i < 100; i++)
					{
						if (Rowfoottotal[i] == true)
						{
							row.CreateCell(i).SetCellValue(Rowfoottotalvalue[i]);
						}
					}
				}
			}

			FileStream outfile = File.Create(fileDialog.FileName);
			hssfworkbook.Write(outfile);

			modefile.Close();
			outfile.Close();

			MessageBox.Show("导出数据成功!", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
			GC.Collect();
		}
	}
}

